package com.ruoyi.generator.sdk;

import com.ruoyi.common.exception.ServiceException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description 数据库链接工具
 * @Author Admin小闫
 * @Date 2024/12/24
 */

public class MySqlConnectionClient {

    private static final Logger log = LoggerFactory.getLogger(MySqlConnectionClient.class);
    /**
     * 数据库IP
     */
    private String mysqlIp;
    /**
     * 数据库端口
     */
    private String mysqlPort;
    /**
     * 数据库名称
     */
    private String databaseName;
    /**
     * 数据库用户名
     */
    private String databaseUsername;
    /**
     * 数据库密码
     */
    private String databasePassword;


    private MySqlConnectionClient(Builder builder) {
        this.mysqlIp = builder.mysqlIp;
        this.mysqlPort = builder.mysqlPort;
        this.databaseName = builder.databaseName;
        this.databaseUsername = builder.databaseUsername;
        this.databasePassword = builder.databasePassword;
    }

    // 内部静态Builder类
    public static class Builder {
        /**
         * 数据库IP
         */
        private String mysqlIp;

        /**
         * 数据库端口
         */
        private String mysqlPort;
        /**
         * 数据库名称
         */
        private String databaseName;
        /**
         * 数据库用户名
         */
        private String databaseUsername;
        /**
         * 数据库密码
         */
        private String databasePassword;




        public Builder() {

        }

        public Builder mySqlIp(String mysqlIp) {
            this.mysqlIp = mysqlIp;
            return this;
        }

        public Builder mySqlPort(String mysqlPort) {
            this.mysqlPort = mysqlPort;
            return this;
        }

        public Builder databaseName(String databaseName) {
            this.databaseName = databaseName;
            return this;
        }

        public Builder databaseUsername(String databaseUsername) {
            this.databaseUsername = databaseUsername;
            return this;
        }

        public Builder databasePassword(String databasePassword) {
            this.databasePassword = databasePassword;
            return this;
        }


        public MySqlConnectionClient build() {
            return new MySqlConnectionClient(this);
        }

    }


    /**
     * 创建一个新的数据库。
     *
     * @param dbName 要创建的数据库名称
     * @throws SQLException 如果创建数据库失败
     */
    public void createDatabase(String dbName) throws SQLException {
        log.info("----------开始创建数据库：{}----------", dbName);
        String sql = "CREATE DATABASE IF NOT EXISTS " + dbName +
                " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;";

        try (Connection connection = getClition();
             Statement stmt = connection.createStatement()) {
            // 执行创建数据库的SQL语句
            int result = stmt.executeUpdate(sql);
            log.info("----------数据库 {} 创建成功----------", dbName);
        } catch (SQLException e) {
            log.error("----------创建数据库失败：{}----------", e.getMessage());
            throw e ;
        }
    }
    /**
     * 测试数据库连接是否成功
     *
     * @return 如果连接成功则返回true，否则抛出SQLException
     * @throws SQLException 如果连接失败
     */
    public boolean testConnection() throws SQLException {
        log.info("----------开始测试数据库连接----------");
        try (Connection connection = getClition()) {
            if (connection != null && !connection.isClosed()) {
                log.info("----------数据库连接成功----------");
                return true;
            } else {
                log.warn("----------数据库连接失败----------");
                return false;
            }
        } catch (SQLException e) {
            log.error("----------数据库连接失败：{}----------", e.getMessage());
            throw e; // 重新抛出异常以让调用者知道连接失败
        }
    }



    public Connection getClition() throws SQLException {
        //2.获取数据库连接对象（这里使用的数据库是jdbcstudy）
        log.info("----------获取数据库链接："+mysqlIp+":"+mysqlPort+":"+databaseName+"----------");
        String url = "jdbc:mysql://" + mysqlIp + ":" + mysqlPort + "/" + databaseName + "?useUnicode=true&characterEncoding=utf8&useSSL=true";
        return DriverManager.getConnection(url, databaseUsername, databasePassword);
        //mysql8需要加上时区
    }


    public List<Map<String, Object>> queryList() throws SQLException {
        List<Map<String, Object>> result = new ArrayList<>();
        String sql = "SELECT table_name AS tableName, engine, table_comment AS tableComment, create_time AS createTime " +
                "FROM information_schema.tables WHERE table_schema = (SELECT DATABASE())";

        sql += " ORDER BY create_time DESC";

        try (Connection connection = getClition();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<>();
                    row.put("tableName", rs.getString("tableName"));
                    row.put("engine", rs.getString("engine"));
                    row.put("tableComment", rs.getString("tableComment"));
                    row.put("createTime", rs.getTimestamp("createTime"));
                    result.add(row);
                }
            }
        }

        return result;
    }

    public Map<String, String> queryTable(String tableName) throws SQLException {
        Map<String, String> result = new HashMap<>();
        String sql = "SELECT table_name AS tableName, engine, table_comment AS tableComment, create_time AS createTime " +
                "FROM information_schema.tables WHERE table_schema = (SELECT DATABASE()) AND table_name = ?";


        try (Connection connection = getClition();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {

            pstmt.setString(1, tableName);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    result.put("tableName", rs.getString("tableName"));
                    result.put("engine", rs.getString("engine"));
                    result.put("tableComment", rs.getString("tableComment"));
                    result.put("createTime", rs.getTimestamp("createTime").toString());
                }
            }
        }

        return result;
    }


    public List<Map<String, String>> queryColumns(String tableName) throws SQLException {
        List<Map<String, String>> result = new ArrayList<>();
        String sql = "SELECT column_name AS columnName, data_type AS dataType, column_comment AS columnComment, " +
                "column_key AS columnKey, extra FROM information_schema.columns " +
                "WHERE table_name = ? AND table_schema = (SELECT DATABASE()) ORDER BY ordinal_position";

        try (Connection connection = getClition();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {

            pstmt.setString(1, tableName);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, String> column = new HashMap<>();
                    column.put("columnName", rs.getString("columnName"));
                    column.put("dataType", rs.getString("dataType"));
                    column.put("columnComment", rs.getString("columnComment"));
                    column.put("columnKey", rs.getString("columnKey"));
                    column.put("extra", rs.getString("extra"));
                    result.add(column);
                }
            }
        }

        return result;
    }

    /**
     * 根据传入的SQL字符串创建表。
     *
     * @param sqlString 包含多个表创建语句的SQL字符串
     * @throws SQLException 如果创建表失败
     */
    public void createTablesFromSqlString(String sqlString) throws SQLException {
        log.info("----------开始根据提供的SQL字符串创建表----------");

        try (Connection connection = getClition()) {
            // 禁用自动提交，以便我们可以对所有表的创建进行批量处理
            connection.setAutoCommit(false);
            try (Statement stmt = connection.createStatement()) {
                // 分割SQL语句字符串为单独的语句
                String[] sqlStatements = sqlString.split(";\\s*");
                for (String sql : sqlStatements) {
                    if (sql.trim().isEmpty()) continue; // 跳过空语句

                    log.info("----------正在执行SQL语句：{}----------", sql.trim());

                    // 执行SQL语句
                    stmt.execute(sql.trim());

                    log.info("----------SQL语句执行成功----------");
                }
                // 提交事务
                connection.commit();
            } catch (SQLException e) {
                // 如果有任何错误发生，回滚所有更改
                connection.rollback();
                log.error("----------创建表失败：{}----------", e.getMessage());
                throw e;
            } finally {
                // 恢复自动提交设置
                connection.setAutoCommit(true);
            }
        }
    }


}